package houxb.cn.excelanalysis;

import com.alibaba.fastjson.JSON;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Analysis {
    public static void main(String[] args) {
        String filePath = "/home/xbhou/alm/ARCU-MAT-T-08-改造类工作量统计表.xlsx";
        System.out.println(JSON.toJSONString(new Analysis().locate(filePath, "需求编号", "需求工作量", "开发工作量", "测试工作量", "质量管理工作量")));
    }

    private List<Map<String, Object>> locate(String filePath, String... colName) {
        List<Map<String, Object>> rows = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath)) {
            Workbook workbook = WorkbookFactory.create(fis);
            for (int sIndex = 0; sIndex < workbook.getNumberOfSheets(); sIndex++) {
                Map<String, Integer> colMap = new HashMap<>();
                Sheet sheet = workbook.getSheetAt(sIndex);
                // 遍历行
                int arIndex = sheet.getPhysicalNumberOfRows(), rIndex = 0;
                while (arIndex > 0) {
                    Row row = sheet.getRow(rIndex++);
                    if (row == null) {
                        continue;
                    }
                    if (colMap.size() == colName.length) {
                        Map<String, Object> r = new HashMap<>();
                        r.put("行号", rIndex);
                        rows.add(r);
                        // 获取数据
                        for (String s : colName) {
                            r.put(s, getCellValue(row.getCell(colMap.get(s)), workbook));
                        }
                    }
                    // 遍历列获取表头所在列
                    int acIndex = row.getPhysicalNumberOfCells(), cIndex = 0;
                    while (acIndex > 0 && colMap.size() < colName.length) {
                        Cell cell = row.getCell(cIndex++);
                        if (cell == null || Cell.CELL_TYPE_STRING != cell.getCellType()) {
                            acIndex--;
                            continue;
                        }
                        for (String s : colName) {
                            if (cell.getStringCellValue().contains(s)) {
                                colMap.put(s, cIndex - 1);
                            }
                        }
                        acIndex--;
                    }
                    arIndex--;
                }
                if (colMap.size() == colName.length) {
                    break;
                }
            }
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        }
        return rows;
    }

    // 关键方法：根据单元格类型解析内容
    private static Object getCellValue(Cell cell, Workbook workbook) {
        if (cell == null) {
            return null;
        }
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue(); // 日期类型
                } else {
                    return cell.getNumericCellValue(); // 数字类型
                }
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return handleFormulaCell(cell, workbook); // 处理公式
            case Cell.CELL_TYPE_BLANK:
                return "";
            default:
                return "未知类型";
        }
    }

    // 处理公式单元格（获取计算后的值）
    private static Object handleFormulaCell(Cell cell, Workbook workbook) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cellValue.getStringValue();
            case Cell.CELL_TYPE_NUMERIC:
                return cellValue.getNumberValue();
            case Cell.CELL_TYPE_BOOLEAN:
                return cellValue.getBooleanValue();
            default:
                return "公式结果未知";
        }
    }
}
